import uuid
import logging
import time
from be.model import db_conn
from be.model import error
from sqlalchemy.exc import SQLAlchemyError
from pymongo.errors import PyMongoError
from sqlalchemy.sql import text
from be.model.times import get_time_stamp
from be.model.encrypt import encrypt
from datetime import datetime,timedelta,timezone
from sqlalchemy import create_engine, text
from pymongo import MongoClient

class Buyer(db_conn.DBConn):
    def __init__(self):
        db_conn.DBConn.__init__(self)
        self.page_size = 20
        

    # 下单
    def new_order(self, user_id: str, store_id: str, id_and_count: list[tuple[str, int]]) -> tuple[int, str, str]:
        order_id = ""
        try:
            with self.conn.begin(): 
                if not self.user_id_exist(user_id):  
                    return error.error_non_exist_user_id(user_id) + (order_id,)
                if not self.store_id_exist(store_id):  
                    return error.error_non_exist_store_id(store_id) + (order_id,)
                             
                uid = "{}_{}_{}".format(user_id, store_id, str(uuid.uuid1()))
                total_price = 0
                
                self.conn.execute(
                    text(
                        "INSERT INTO orders (order_id, store_id, user_id, status, total_price, created_at) "
                        "VALUES (:uid, :store_id, :user_id, :status, :total_price, :created_at)"
                    ),
                    {"uid": uid, "store_id": store_id, "user_id": user_id, "status": 1, "total_price": 0, "created_at": get_time_stamp()}
                )

                for book_id, count in id_and_count:
                    cursor = self.conn.execute(
                        text(
                            "UPDATE store_book SET stock_level = stock_level - :count "
                            "WHERE store_id = :store_id AND book_id = :book_id AND stock_level >= :count "
                            "RETURNING price"
                        ),
                        {"count": count, "store_id": store_id, "book_id": book_id}
                    )
                    
                    if cursor.rowcount == 0:
                        return error.error_stock_level_low(book_id) + (order_id,)

                    row = cursor.fetchone()
                    price = row[0]
                    total_price += count * price

                    self.conn.execute(
                        text(
                            "INSERT INTO order_book (order_id, book_id, count) "
                            "VALUES (:uid, :book_id, :count)"
                        ),
                        {"uid": uid, "book_id": book_id, "count": count}
                    )

                self.conn.execute(
                    text(
                        "UPDATE orders SET total_price = :total_price WHERE order_id = :uid"
                    ),
                    {"uid": uid, "total_price": total_price}
                )
                
                order_id = uid  

            return 200, "ok", order_id

        except SQLAlchemyError as e:
            logging.error(f"SQLAlchemyError occurred: {str(e)}")
            return 528, "{}".format(str(e)), ""
        except BaseException as e:
            logging.error(f"BaseException occurred: {str(e)}")
            return 530, "{}".format(str(e)), ""

    # 付款
    def payment(self, user_id: str, password: str, order_id: str) -> tuple[int, str]:
        conn = self.conn
        try:
            with self.conn.begin():
                cursor = conn.execute(text("SELECT * FROM orders WHERE order_id = :order_id"),
                                    {"order_id": order_id})
                row = cursor.fetchone()
                if row is None:
                    return error.error_invalid_order_id(order_id)

                order_id = row[0]
                buyer_id = row[1]
                store_id = row[2]
                total_price = row[4]
                order_time = row[5]
                status = row[3]

                if buyer_id != user_id:
                    return error.error_authorization_fail()
                if status != 1:
                    return error.error_invalid_order_status()

                cursor = conn.execute(text("SELECT balance, password FROM users WHERE user_id = :buyer_id;"),
                                    {"buyer_id": buyer_id})
                row = cursor.fetchone()
                if row is None:
                    return error.error_non_exist_user_id(buyer_id)
                balance = row[0]
                if encrypt(password) != row[1]:
                    return error.error_authorization_fail()
                if balance < total_price:
                    return error.error_not_sufficient_funds(order_id)

                cursor = conn.execute(text("UPDATE users SET balance = balance - :total_price1 "
                                        "WHERE user_id = :buyer_id AND balance >= :total_price2"),
                                    {"total_price1": total_price, "buyer_id": buyer_id, "total_price2": total_price})
                if cursor.rowcount == 0:
                    return error.error_unknown("update_user_error")

                self.conn.execute(
                    text("UPDATE orders SET status = 2, paid_at = :current_time WHERE order_id = :order_id;"),
                    {"order_id": order_id, "current_time": get_time_stamp()}
                )

                # self.conn.commit()

            return 200, "ok"

        except SQLAlchemyError as e:
            print(f"SQLAlchemyError occurred: {str(e)}")  
            return 528, "{}".format(str(e))
        except BaseException as e:
            print(f"BaseException occurred: {str(e)}") 
            return 530, "{}".format(str(e))

    # 充值
    def add_funds(self, user_id, password, add_value) -> tuple[int, str]:  
        try:
            with self.conn.begin():  
                cursor = self.conn.execute(text("SELECT password from users where user_id = :user_id"), {"user_id": user_id})
                row = cursor.fetchone()
                if row is None:
                    return error.error_authorization_fail()

                if row[0] != encrypt(password):
                    return error.error_authorization_fail()

                cursor = self.conn.execute(
                    text("UPDATE users SET balance = balance + :add_value WHERE user_id = :user_id"),
                    {"add_value": add_value, "user_id": user_id})
                if cursor.rowcount == 0:
                    return error.error_non_exist_user_id(user_id)

                # self.conn.commit()

        except SQLAlchemyError as e:
            return 528, "{}".format(str(e))
        except BaseException as e:
            return 530, "{}".format(str(e))

        return 200, "ok"

    # 确认收货
    def receive_books(self, user_id: str, password: str, order_id: str) -> tuple[int, str]:
        try:
            with self.conn.begin(): 
                if not self.user_id_exist(user_id):
                    return error.error_non_exist_user_id(user_id)
                if not self.order_id_exist(order_id):  
                    return error.error_invalid_order_id(order_id)

                cursor = self.conn.execute(text("SELECT order_id, user_id, store_id, total_price, status FROM orders WHERE order_id = :order_id"),
                                            {"order_id": order_id, })
                row = cursor.fetchone()

                if row is None:
                    return error.error_invalid_order_id(order_id)

                order_id = row[0]
                buyer_id = row[1]
                store_id = row[2]
                total_price = row[3]
                status = row[4]

                if buyer_id != user_id:
                    return error.error_authorization_fail()
                if status != 3:
                    return error.error_invalid_order_status(order_id)

                cursor = self.conn.execute(text("SELECT store_id, user_id FROM user_store WHERE store_id = :store_id;"),
                                            {"store_id": store_id, })
                row = cursor.fetchone()
                if row is None:
                    return error.error_non_exist_store_id(store_id)

                seller_id = row[1]

                if not self.user_id_exist(seller_id):
                    return error.error_non_exist_user_id(seller_id)

                cursor = self.conn.execute(text("UPDATE users set balance = balance + :total_price "
                                                "WHERE user_id = :seller_id"),
                                            {"total_price": total_price, "seller_id": seller_id})

                if cursor.rowcount == 0:
                    return error.error_non_exist_user_id(buyer_id)

                self.conn.execute(
                    text("UPDATE orders SET status = 4, received_at = :current_time WHERE order_id = :order_id;"),
                    {"order_id": order_id, "current_time": get_time_stamp()}
                )

                # self.conn.commit()

        except SQLAlchemyError as e:
            print(f"SQLAlchemyError occurred: {str(e)}")
            return 528, "{}".format(str(e))
        except BaseException as e:
            return 530, "{}".format(str(e))

        return 200, "ok"
    
    # 取消订单
    def user_cancel_order(self, buyer_id, order_id) -> tuple[int, str]:
        try:
            with self.conn.begin():
                if not self.user_id_exist(buyer_id):
                    return error.error_non_exist_user_id(buyer_id)
                if not self.order_id_exist(order_id):
                    return error.error_invalid_order_id(order_id)

                cursor = self.conn.execute(text("SELECT status FROM orders WHERE order_id = :order_id AND user_id=:user_id;"),
                                        {"order_id": order_id, "user_id": buyer_id })
                if not cursor.fetchone():
                    return error.error_authorization_fail()

                cursor = self.conn.execute(text("SELECT status, store_id, total_price FROM orders WHERE order_id = :order_id;"),
                                        {"order_id": order_id, })
                row = cursor.fetchone()

                if row[0] != 1 and row[0] != 2: 
                    return error.error_invalid_order_status(order_id)

                cursor = self.conn.execute(text("SELECT book_id, count FROM order_book WHERE order_id = :order_id;"),
                                            {"order_id": order_id, })
                order_books = cursor.fetchall()

                for book in order_books:
                    book_id, count = book
                    store_id = row[1] 
                    self.conn.execute(text(
                        "UPDATE store_book SET stock_level = stock_level + :count WHERE store_id = :store_id AND book_id = :book_id;"),
                        {"store_id": store_id, "book_id": book_id, "count": count})

                if row[0] == 2:
                    self.conn.execute(text(
                        "UPDATE users SET balance = balance + :money WHERE user_id = :user_id;"),
                        {"money": row[2], "user_id": buyer_id})

                self.conn.execute(text(
                    "UPDATE orders SET status = 0 WHERE order_id = :order_id;"), {"order_id": order_id})

                # self.conn.commit()

        except SQLAlchemyError as e:
            return 528, "{}".format(str(e))
        except BaseException as e:
            return 530, "{}".format(str(e))

        return 200, "ok"
    
    # 自动取消订单
    def auto_cancel_order(self, user_id: str, order_id: str) -> tuple[int, str]:
        try:
            with self.conn.begin():
                if not self.user_id_exist(user_id):
                    return error.error_non_exist_user_id(user_id)
                if not self.order_id_exist(order_id):
                    return error.error_invalid_order_id(order_id)

                cursor = self.conn.execute(text("SELECT status, store_id, created_at FROM orders WHERE order_id = :order_id;"),
                                            {"order_id": order_id, })
                row = cursor.fetchone()
                if row[0] != 1:  
                    return error.error_invalid_order_status(order_id)

                # 订单创建时间阈值为1分钟，仅为了方便测试
                created_at = row[2]
                created_at = datetime.fromtimestamp(created_at, timezone.utc)

                current_time = datetime.now(timezone.utc) 
                if current_time - created_at > timedelta(minutes=1):

                    cursor = self.conn.execute(text("SELECT book_id, count FROM order_book WHERE order_id = :order_id;"),
                                                {"order_id": order_id, })
                    order_books = cursor.fetchall()

                    for book in order_books:
                        book_id, count = book
                        store_id = row[1]
                        self.conn.execute(text(
                            "UPDATE store_book SET stock_level = stock_level + :count WHERE store_id = :store_id AND book_id = :book_id;"),
                            {"store_id": store_id, "book_id": book_id, "count": count})

                    self.conn.execute(text(
                        "UPDATE orders SET status = 0 WHERE order_id = :order_id;"), {"order_id": order_id})

                    # self.conn.commit()

                    return 200, "auto cancelled successfully"
                else:
                    return 403, "created time less than limit"

        except SQLAlchemyError as e:
            return 528, "{}".format(str(e))
        except BaseException as e:
            return 530, "{}".format(str(e))

    #查看历史订单
    def get_orders(self, user_id: str) -> tuple[int, list]:
        try:
            with self.conn.begin():
                if not self.user_id_exist(user_id):
                    return error.error_non_exist_user_id(user_id)

                result = self.conn.execute(text(
                    "SELECT order_id FROM orders WHERE user_id = :user_id AND status IN (0, 4);"
                ), {"user_id": user_id}).fetchall()

                order_list = [order[0] for order in result]

                return 200, order_list

        except SQLAlchemyError as e:
            return 528, "{}".format(str(e))
        except BaseException as e:
            return 530, "{}".format(str(e))
    
    # 书籍检索
    def search_books(self, search_key, store_id=None, page=1):
        try:
            offset = (page - 1) * self.page_size

            search_key = search_key.strip()

            base_query = """
                SELECT id, title, tags 
                FROM book 
                WHERE search_vector @@ to_tsquery(:search_key)
            """

            if store_id:
                base_query += " AND id IN (SELECT book_id FROM store_book WHERE store_id = :store_id)"
            base_query += " ORDER BY id LIMIT :limit OFFSET :offset"

            with self.conn as conn:
                result = conn.execute(
                    text(base_query),
                    {
                        "search_key": search_key,
                        "store_id": store_id,
                        "limit": self.page_size,
                        "offset": offset,
                    },
                ).fetchall()

            print(search_key)
            
            books = [
                {
                    "id": row[0],
                    "title": row[1],
                    "tags": row[2],
                }
                for row in result
            ]

            book_ids = [book["id"] for book in books]
            mongo_details = list(self.mongo.book_details.find({"book_id": {"$in": book_ids}}, {"_id": 0}))

            for book in books:
                details = next((item for item in mongo_details if item["book_id"] == book["id"]), {})
                book.update(details)
            
            return 200, "ok", books

        except SQLAlchemyError as e:
            logging.error(f"PostgreSQL Error: {str(e)}")
            return 528, f"PostgreSQL Error: {str(e)}", []
        except PyMongoError as e:
            logging.error(f"MongoDB Error: {str(e)}")
            return 529, f"MongoDB Error: {str(e)}", []
        except Exception as e:
            logging.error(f"Unknown Error: {str(e)}")
            return 530, f"Unknown Error: {str(e)}", []
